﻿using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    public class FixedAssestChangeDAL
    {
        /// <summary>
        /// 获取固定资产变更列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="assetTypeID"></param>
        /// <param name="materialID"></param>
        /// <param name="assetID"></param>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public object GetChangeList(int pageSize, int pageIndex, string assetTypeID, string materialID, string assetID, DateTime DateStart, DateTime DateEnd)
        {
            List<FIXED_PermissionChangeInfo> list = new List<FIXED_PermissionChangeInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct identity(int,1,1) as 行号 ");
            sb.Append(" ,fpc.编码,fpc.资产编码,fpc.变更项目,fpc.变更理由,fpc.变更日期,fpc.移交人员,fpc.接收人员 ");
            //sb.Append(" ,fpc.移交部门,fpc.接收部门,fpc.分管主任 ");
            //sb.Append(" ,资产名称=fm.资产名称,固定资产编号=fm.固定资产编码,资产管理人=fm.资产管理人 ");
            //edit 2019-11-07 plq 变更表增加资产管理人，记录变更前的资产管理人，而非取变更后以接收人员为基本信息表的资产管理人
            sb.Append(" ,fpc.移交部门,fpc.接收部门,fpc.资产管理人,fpc.分管主任 ");
            sb.Append(" ,资产名称=fm.资产名称,固定资产编号=fm.固定资产编码 ");
            sb.Append(" into #temp ");
            sb.Append(@"   
                from FIXED_PermissionChange fpc ");
            sb.Append(@"   
                left join FIXED_Matetial fm on fm.编码=fpc.资产编码 ");
            sb.Append(@"
                where 1=1 ");
            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", assetTypeID, sb);
            if ((assetTypeID.Length > 0) && (assetTypeID != null) && (assetTypeID != "-1") && (assetTypeID != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(assetTypeID); //分类编码list
                //string s1 = string.Join(",", typeIDList.ToArray()); //将list转为string，逗号分隔
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("fm.类型编码", s1, sb);
            }
            //资产编码筛选
            if ((materialID != null) && (materialID != "") && (materialID != "-1") && materialID != "--请选择--")
            {
                Guid MaterialID = new Guid(materialID);
                sb.Append(@" and fpc.资产编码='" + MaterialID + "'");
            }
            WhereClauseUtility.AddStringEqual("fm.固定资产编码", assetID, sb);

            WhereClauseUtility.AddDateTimeGreaterThan("变更日期", DateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("变更日期", DateEnd, sb);

            sb.Append(@"
                order by 变更日期,资产名称 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #temp ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_PermissionChangeInfo info = new FIXED_PermissionChangeInfo();
                        info.BianMa = Convert.ToInt32(dr["编码"].ToString());
                        info.ZiChanBianMa = new Guid(dr["资产编码"].ToString());
                        info.BianGengXiangMu = dr["变更项目"].ToString();
                        info.BianGengLiYou = dr["变更理由"].ToString();
                        info.BianGengRiQi = Convert.ToDateTime(dr["变更日期"].ToString());
                        info.YiJiaoRenYuan = dr["移交人员"].ToString();
                        info.JieShouRenYuan = dr["接收人员"].ToString();
                        info.YiJiaoBuMen = dr["移交部门"].ToString();
                        info.JieShouBuMen = dr["接收部门"].ToString();
                        info.FenGuanZhuRen = dr["分管主任"].ToString();

                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.GuDingZiChanBianHao = dr["固定资产编号"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();

                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/GetChangeList", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="assetTypeID"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string assetTypeID)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + assetTypeID + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(dr["ID"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取变更记录数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_PermissionChangeInfo GetChangeData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            //Guid Code1 = new Guid(Code);
            int Code1 = Convert.ToInt32(Code);
            sb.Append(@" select fpc.* ");
            sb.Append(@" from FIXED_PermissionChange fpc ");
            sb.Append(@" 
                        where 1=1 ");
            //sb.Append(" and 编码='" + Code1 + "'");
            sb.Append(" and 编码=" + Code1 + "");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_PermissionChangeInfo()
                            {
                                BianMa = a.Field<int>("编码"),
                                ZiChanBianMa = a.Field<Guid>("资产编码"),
                                BianGengRiQi = a.Field<DateTime>("变更日期"),
                                BianGengXiangMu = a.Field<string>("变更项目"),
                                BianGengLiYou = a.Field<string>("变更理由"),
                                YiJiaoBuMen = a.Field<string>("移交部门"),
                                JieShouBuMen = a.Field<string>("接收部门"),
                                YiJiaoRenYuan = a.Field<string>("移交人员"),
                                JieShouRenYuan = a.Field<string>("接收人员"),
                                ZiChanGuanLiRen = a.Field<string>("资产管理人"), //add20191107 plq
                                FenGuanZhuRen = a.Field<string>("分管主任"),
                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/GetChangeData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增变更---变更表数据插入，同时更新基本信息表资产管理人、资产管理部门字段
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertEditPageData(FIXED_PermissionChangeInfo info)
        {
            bool result = false;
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                {

                    bool result1 = InsertTableChange(info);
                    bool result2 = UpdateTableMatetrial(info);

                    if (result1 && result2)
                    {
                        result = true;
                    }
                    ts.Complete(); //提交事务
                }
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/InsertEditPageData", ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 向资产变更表插入数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool InsertTableChange(FIXED_PermissionChangeInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            info.BianMa = GetMaxID("FIXED_PermissionChange") + 1;//获取表的编码最大值+1
            sb.Append(" insert into FIXED_PermissionChange values (");
            sb.Append(" " + info.BianMa + ", ");
            sb.Append(" '" + info.ZiChanBianMa + "', ");
            sb.Append(" '" + info.BianGengXiangMu + "', ");
            sb.Append(" '" + info.BianGengLiYou + "', ");
            sb.Append(" '" + info.BianGengRiQi + "', ");
            sb.Append(" '" + info.YiJiaoRenYuan + "', ");
            sb.Append(" '" + info.JieShouRenYuan + "', ");
            sb.Append(" '" + info.YiJiaoBuMen + "', ");
            sb.Append(" '" + info.JieShouBuMen + "', ");
            sb.Append(" '" + info.ZiChanGuanLiRen + "', "); //add20191107 plq 资产管理人
            sb.Append(" '" + info.FenGuanZhuRen + "' ");
            sb.Append(" )");

            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/InsertTableChange", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 获取表的编码最大值
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        private int GetMaxID(string tablename)
        {
            int result = 0;
            StringBuilder sb = new StringBuilder();
            sb.Append(@" select 编码 from " + tablename + " where 编码 in (select max(cast(编码 as int)) from " + tablename + ") ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                if (dr.Read())
                {
                    result = Convert.ToInt32(dr["编码"]);
                }
                return result;
            }
        }

        /// <summary>
        /// 更新资产基本信息表部分字段
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool UpdateTableMatetrial(FIXED_PermissionChangeInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Matetial set ");
            sb.Append(@" 
                        资产管理部门='" + info.JieShouBuMen + "', ");
            sb.Append(" 资产管理人='" + info.JieShouRenYuan + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= '" + info.ZiChanBianMa + "'");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/UpdateTableMatetrial", ex.Message);
                result = false;
            }
            return result;
        }


        /// <summary>
        /// 修改变更---变更表数据修改，同时更新基本信息表资产管理人、资产管理部门字段
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateEditPageData(FIXED_PermissionChangeInfo info)
        {
            bool result = false;
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                {

                    bool result1 = UpdateTableChange(info);
                    bool result2 = UpdateTableMatetrial(info);

                    if (result1 && result2)
                    {
                        result = true;
                    }
                    ts.Complete(); //提交事务
                }
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/UpdateEditPageData", ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 更新资产变更数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool UpdateTableChange(FIXED_PermissionChangeInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_PermissionChange set ");
            sb.Append(@" 
                        变更日期='" + info.BianGengRiQi + "', ");
            sb.Append(" 变更项目='" + info.BianGengXiangMu + "', ");
            sb.Append(" 变更理由='" + info.BianGengLiYou + "', ");
            //add 2019-10-08 plq 移交部门、移交人员、资产管理人改为可编辑
            sb.Append(" 移交部门='" + info.YiJiaoBuMen + "', ");
            sb.Append(" 移交人员='" + info.YiJiaoRenYuan + "', ");
            sb.Append(" 资产管理人='" + info.ZiChanGuanLiRen + "', ");
            sb.Append(" 接收部门='" + info.JieShouBuMen + "', ");
            sb.Append(" 接收人员='" + info.JieShouRenYuan + "', ");
            sb.Append(" 分管主任='" + info.FenGuanZhuRen + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= " + info.BianMa + "");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssestChangeDAL/UpdateTableChange", ex.Message);
                result = false;
            }
            return result;
        }
    }
}
